const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

async function initDatabase() {
  let connection;
  try {
    console.log('正在连接数据库...');
    console.log('连接信息:', {
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME
    });

    // 创建数据库连接
    connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    });

    console.log('数据库连接成功');

    // 删除现有数据库（如果存在）
    await connection.query(`DROP DATABASE IF EXISTS ${process.env.DB_NAME}`);
    console.log('删除现有数据库成功');

    // 创建数据库
    await connection.query(`CREATE DATABASE ${process.env.DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`);
    console.log('数据库创建成功');

    // 使用数据库
    await connection.query(`USE ${process.env.DB_NAME}`);
    console.log('切换到数据库成功');

    // 创建用户表
    await connection.query(`
      CREATE TABLE Users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(20) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password VARCHAR(100) NOT NULL,
        role ENUM('user', 'admin') DEFAULT 'user',
        avatar VARCHAR(255),
        createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
        updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `);
    console.log('用户表创建成功');

    // 创建骗子信息表
    await connection.query(`
      CREATE TABLE Scams (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(100) NOT NULL,
        phone VARCHAR(20) NOT NULL,
        description TEXT NOT NULL,
        evidence JSON,
        status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
        views INT DEFAULT 0,
        commentCount INT DEFAULT 0,
        userId INT NOT NULL,
        createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
        updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (userId) REFERENCES Users(id) ON DELETE CASCADE
      )
    `);
    console.log('骗子信息表创建成功');

    // 创建评论表
    await connection.query(`
      CREATE TABLE Comments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        content TEXT NOT NULL,
        status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
        userId INT NOT NULL,
        scamId INT NOT NULL,
        createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
        updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (userId) REFERENCES Users(id) ON DELETE CASCADE,
        FOREIGN KEY (scamId) REFERENCES Scams(id) ON DELETE CASCADE
      )
    `);
    console.log('评论表创建成功');

    // 创建管理员账号
    const adminPassword = '$2a$10$XOPbrlUPQdwdJUpSrIF6X.LbE14qsMmKGhM1A8W9iq.0Zz0YqXK6'; // admin123
    await connection.query(`
      INSERT INTO Users (username, email, password, role) 
      VALUES ('admin', 'admin@example.com', ?, 'admin')
    `, [adminPassword]);
    console.log('管理员账号创建成功');

    // 验证数据库和表是否创建成功
    const [databases] = await connection.query('SHOW DATABASES');
    console.log('现有数据库:', databases.map(db => db.Database));

    const [tables] = await connection.query('SHOW TABLES');
    console.log('现有表:', tables.map(table => Object.values(table)[0]));

    await connection.end();
    console.log('数据库初始化完成');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    if (connection) {
      await connection.end();
    }
    process.exit(1);
  }
}

// 如果直接运行此文件
if (require.main === module) {
  initDatabase();
}

module.exports = initDatabase; 